/*
Retrieves a list of all of the DocumentVersionKeys for all documents that are descendants of the provided folder key (i.e. its children, children's children, etc).
*/
CREATE PROCEDURE [dbo].[asi_DocumentDescendantKeys]
(
      @hierarchyKey uniqueidentifier,
      @userKey uniqueidentifier,
      @loggedInUserGroupKey uniqueidentifier = '00000000-0000-0000-0000-000000000000', -- if this is empty, we assume the user is not logged in
      @publishedOnly bit = 1,
      @ignoreLicensing bit = 0
)
AS
BEGIN
      DECLARE @descendants TABLE
      (
            [RootHierarchyKey] uniqueidentifier,
            [HierarchyKey] uniqueidentifier,
            [DocumentVersionKey] uniqueidentifier,
            [DocumentKey] uniqueidentifier,
            [AccessKey] uniqueidentifier
      )

      INSERT INTO @descendants
      SELECT h.[RootHierarchyKey], h.[HierarchyKey], d.[DocumentVersionKey], d.[DocumentKey], d.[AccessKey]
      FROM [Hierarchy] h
            INNER JOIN [DocumentMain] d ON h.[UniformKey] = d.[DocumentVersionKey]
      WHERE h.[ParentHierarchyKey] = @hierarchyKey
            AND ((@publishedOnly = 1 AND d.[DocumentStatusCode] IN (40,60)) OR
                     (@publishedOnly = 0 AND d.DocumentKey IN (SELECT TOP 1 [DocumentKey] FROM [DocumentMain] WHERE [DocumentVersionKey] = d.[DocumentVersionKey] AND [DocumentStatusCode] IN (10,20,30,40,60) ORDER BY [DocumentStatusCode])))

      WHILE (@@ROWCOUNT > 0)
      BEGIN
            INSERT INTO @descendants
            SELECT h.[RootHierarchyKey], h.[HierarchyKey], d.[DocumentVersionKey], d.[DocumentKey], d.[AccessKey]
            FROM [Hierarchy] h
                  INNER JOIN [DocumentMain] d ON h.[UniformKey] = d.[DocumentVersionKey]
                  INNER JOIN @descendants d1 ON h.[ParentHierarchyKey] = d1.HierarchyKey
                  LEFT OUTER JOIN @descendants d2 ON h.[HierarchyKey] = d2.[HierarchyKey]
            WHERE d2.[HierarchyKey] IS NULL
                  AND ((@publishedOnly = 1 AND d.[DocumentStatusCode] IN (40,60)) OR
                         (@publishedOnly = 0 AND d.DocumentKey IN (SELECT TOP 1 [DocumentKey] FROM [DocumentMain] WHERE [DocumentVersionKey] = d.[DocumentVersionKey] AND [DocumentStatusCode] IN (10,20,30,40,60) ORDER BY [DocumentStatusCode])))
      END
      SELECT [RootHierarchyKey], [HierarchyKey], [DocumentVersionKey], [DocumentKey]
        FROM @descendants d
        INNER JOIN [dbo].[AccessItem] ai ON d.[AccessKey] = ai.[AccessKey]
        INNER JOIN [dbo].[UserToken] ut ON (ai.[Grantee] = ut.[Grantee] OR ai.Grantee = @loggedInUserGroupKey) AND ut.[UserKey] = @userKey
        LEFT OUTER JOIN [dbo].[UniformLicense] ul ON d.[DocumentVersionKey] = ul.[UniformKey]
        LEFT OUTER JOIN [dbo].[LicenseLegacyList] ll ON ul.[LicenseKey] = ll.[LicenseLegacyKey]
        WHERE (@ignoreLicensing = 1 OR (ul.[LicenseKey] IS NULL OR ll.[LicenseLegacyKey] IS NOT NULL))   
        GROUP BY [RootHierarchyKey], [HierarchyKey], [DocumentVersionKey], [DocumentKey]
      END



GO